package com.sec.bnjey.database;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;

import com.sec.bnjey.utils.RbLog;

public class DtcDbAdapter {

	private static DtcDbAdapter mDbAdapter;
	private DtcDbHelper mDbHelper;
	private SQLiteDatabase mDb;
	private Context mCtx;

	public static synchronized DtcDbAdapter getInstance(Context context) {
		if (mDbAdapter == null) {
			mDbAdapter = new DtcDbAdapter(context);
		}
		return mDbAdapter;
	}

	private DtcDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public DtcDbAdapter open() throws SQLException {

		if (mDbHelper == null) {
			mDbHelper = new DtcDbHelper(mCtx);
			try {
				mDb = mDbHelper.getWritableDatabase();
			} catch (SQLException ex) {
				mDb = mDbHelper.getReadableDatabase();
			}
		}
		return this;
	}

	public void close() {
		if (mDbHelper != null) {
			mDbHelper.close();
			mDbHelper = null;
		}
		if (mDb != null) {
			mDb.close();
			mDb = null;
		}
	}

	public long insert(DtcInfo info) {
		ContentValues args = new ContentValues();
		args.put(DtcDbHelper.CODE, info.getCode());
		args.put(DtcDbHelper.INFO_ENG, info.getInfoEng());
		args.put(DtcDbHelper.INFO_KOR, info.getInfoKor());
		args.put(DtcDbHelper.SUB_INFO_KOR, info.getSubInfoKor());
		return mDb.insert(DtcDbHelper.DATABASE_TABLE, null, args);
	}

	public boolean remove(String code) {
		return mDb.delete(DtcDbHelper.DATABASE_TABLE, DtcDbHelper.CODE + "="
				+ code, null) > 0;
	}

	public DtcInfo get(String code) {
		DtcInfo info = null;

		try {
			Cursor cursor = mDb.query(DtcDbHelper.DATABASE_TABLE, new String[] {
					DtcDbHelper.PKEY, DtcDbHelper.CODE, DtcDbHelper.INFO_ENG,
					DtcDbHelper.INFO_KOR, DtcDbHelper.SUB_INFO_KOR },
					DtcDbHelper.CODE + " = ?", new String[] { code }, null,
					null, null);

			if (cursor.getCount() == 1) {
				cursor.moveToFirst();
				info = new DtcInfo();
				info.setPkey(cursor.getInt(0));
				info.setCode(cursor.getString(1));
				info.setInfoEng(cursor.getString(2));
				info.setInfoKor(cursor.getString(3));
				info.setSubInfoKor(cursor.getString(4));
			}
			cursor.close();

		} catch (SQLiteException e) {

		}

		return info;
	}

	public boolean removeAll() {
		return mDb.delete(DtcDbHelper.DATABASE_TABLE, null, null) > 0;
	}

	private Cursor getAll() {
		return mDb.query(DtcDbHelper.DATABASE_TABLE, new String[] {
				DtcDbHelper.PKEY, DtcDbHelper.CODE, DtcDbHelper.INFO_ENG,
				DtcDbHelper.INFO_KOR, DtcDbHelper.SUB_INFO_KOR }, null, null,
				null, null, null);
	}

	public ArrayList<DtcInfo> getInfoList() {
		ArrayList<DtcInfo> list = new ArrayList<DtcInfo>();
		DtcInfo info;
		Cursor cursor = getAll();
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			info = new DtcInfo();
			info.setPkey(cursor.getInt(0));
			info.setCode(cursor.getString(1));
			info.setInfoEng(cursor.getString(2));
			info.setInfoKor(cursor.getString(3));
			info.setSubInfoKor(cursor.getString(4));
			list.add(info);
			cursor.moveToNext();
		}
		cursor.close();
		return list;
	}

	public static void createFromCsvFile(Context context) {

		DtcDbAdapter dbAdapter = null;

		dbAdapter = DtcDbAdapter.getInstance(context);

		dbAdapter.open();

		InputStream in = null;
		ArrayList<String> arrayList = new ArrayList<String>();

		try {
			in = context.getResources().getAssets().open("codeinfotable.csv");

			ByteArrayOutputStream bos = new ByteArrayOutputStream();
			boolean quot = false;
			while (true) {

				int b = in.read();

				if (b == -1) { // end of file
					if (bos.size() > 0)
						arrayList.add(bos.toString("EUC-KR"));
					bos.reset();
					break;
				}

				// '"' -> '' , '""' -> '"'
				if (b == '"') {
					if (quot) {
						quot = false;
					} else {
						quot = true;
						continue;
					}
				} else {
					quot = false;
				}

				if (b == '\r') {
					continue;
				}
				if (b == '\n') {
					if (bos.size() > 0)
						arrayList.add(bos.toString("EUC-KR"));
					bos.reset();
					continue;
				}

				bos.write(b);
			}

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		for (int i = 0; i < arrayList.size(); i++) {
			String line = arrayList.get(i);

			String[] fields = line.split(",");
			DtcInfo info = new DtcInfo();
			if (fields.length == 4) {
				info.setCode(fields[0]);
				info.setInfoEng(fields[1]);
				info.setInfoKor(fields[2]);
				info.setSubInfoKor(fields[3]);
			} else {
				info.setCode(fields[0]);
				info.setInfoEng(fields[1]);
				info.setInfoKor(fields[2]);
				info.setSubInfoKor("");
			}
			dbAdapter.insert(info);

		}

		ArrayList<DtcInfo> infoList = dbAdapter.getInfoList();

		for (int j = 0; j < infoList.size(); j++) {
			DtcInfo info = infoList.get(j);
			RbLog.d("XML",
					info.getCode() + ":" + info.getInfoEng() + ":"
							+ info.getInfoKor() + ":" + info.getSubInfoKor());
		}
		if (dbAdapter != null) {
			dbAdapter.close();
		}
	}

}
